﻿using Excel;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.IO;
using System.Linq;

namespace excelmysql
{
    class Program
    {
        static void Main(string[] args)
        {
            var conString = args[0];
            var fileName = args[1];

            //открыть файл *.xlsx
            var ds = DataSetFromXLSX(fileName);

            //попытаться обновить каждую таблицу
            foreach (var table in ds.Tables.Cast<DataTable>())
            {
                UpdateTable(table, conString);
            }
        }

        static void UpdateTable(DataTable table, string conString)
        {
            try
            {
                var adapter = new MySqlDataAdapter()
                {
                    SelectCommand = new MySqlCommand()
                    {
                        Connection = new MySqlConnection(conString),
                        CommandType = CommandType.TableDirect,
                        CommandText = table.TableName
                    }
                };
                var builder = new MySqlCommandBuilder(adapter);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                adapter.InsertCommand = builder.GetInsertCommand();

                var dt = new DataTable();

                adapter.Fill(dt);

                foreach (var row in dt.AsEnumerable().ToArray())
                {
                    row.Delete();
                }

                foreach (var row in table.AsEnumerable())
                {
                    dt.LoadDataRow(row.ItemArray, false);
                }

                adapter.Update(dt);
                Console.WriteLine(table.TableName + " update successful");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        static DataTable ReadDataXLSX(string fileName)
        {
            FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            DataTable dt = new DataTable();
            dt = result.Tables[0];
            return dt;
        }

        static DataSet DataSetFromXLSX(string fileName)
        {
            DataSet result = null;

            using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                excelReader.IsFirstRowAsColumnNames = true;
                result = excelReader.AsDataSet();
            }

            return result;
        }
    }
}
